
-- Project: SQL Cars Data
-- Description: 
	-- This is a database where there is cars data.
	-- There will be multiple tables created to find different things needed.
	-- Also, there are many columns about each are, which are different from each other.
	-- In this program we are using the commands, such as WHERE, DESC, ASC, and ORDER BY.
-- Jamila Kandawala
-- 11/12/2024


create database Cars; -- This is creating a database called Cars, which will allow you to create a table inside of it.

use Cars; -- This is a command use that will make everything below go into the database.

-- This is creating a table called CarsData, which will have many columns and data stored inside of it to make sure it stays organized.
create table CarsData(
	-- This is making the primary key be auto_increment, which means that it has no value.
	id int primary key auto_increment, 
	Years YEAR, -- This will format the year data specifically, which will only allow 4 digits to make up the year. 
	Make varchar(20), -- This is the Make column, whih will be what the car is and as like the brand, which will have up to 20 characters.
	Model varchar(50), -- This is the Model column, which will show what model the car is and it will have up to 50 chararcters.
	Country varchar(20), -- This is the Country column, which will show what country the car is from and it will have up to 20 characters.
	Cycl varchar(1), -- This is the cyclinder column, which will have up to 1 character, so it will be 1 digit. 
	Engine varchar(5), -- This is the Engine column to show what engine each are has, which will have up to 5 characters.
	MPG varchar(5), -- This is the MPG column for each car, which will have up to 5 characters.
	Price varchar(10), -- This is the price column of the price of the car, which will have up to 10 characters.
	Doors varchar(1), -- This is the doors column to show how many doors the car has, which will have only 1 character.
	KEY(Make), -- This will make the Make column have a key, which is shown in the directions.
	KEY(Model) -- This will make the Model column have a key, which is shown in the directions.
);

-- This is describing the Carsdata, which is the name of the table. It will describe the columns and the data in each column too, which is very important. 
describe CarsData;

-- This is showing that the values below will be put into the CarsData table, which is what inset into does. 
insert into CarsData 
(Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors) -- This is the order of the data that will be put into each column. 
-- This is where the values will be inputted and how the order will be for it.
values
('2012', 'Nissan', 'Versa', 'Japan', '4', '1.6', '31', '10990', '4'), -- This is the Nissan Versa 2012 car data.
('2013', 'Nissan', 'Versa', 'Japan', '4', '1.6', '31', '11990', '4'), -- This is the Nissan Versa 2013 car data.
('2013', 'Chevrolet', 'Spark', 'United States', '4', '1.2', '35', '12185', '2'), -- This is the Chevrolt Spark 2013 car data.
('2013', 'Smart', 'Fortwo', 'Germany', '3', '1', '36', '12490', '2'), -- This is the Smart Fortwo 2013 car data.
('2012', 'Hyundai', 'Accent', 'South Korea', '4', '1.6', '32', '12545', '4'), -- This is the Hyundai Accent 2012 car data. 
('2013', 'Ford', 'Fiesta', 'United States', '4', '1.6', '33', '13400', '4'), -- This is the Ford Fiesta 2013 car data.
('2012', 'Kia', 'Rio', 'South Korea', '4', '1.6', '33', '13600', '4'), -- This is the Kia Rio 2012 car data.
('2012', 'Kia', 'Rio5', 'South Korea', '4', '1.6', '33', '13600', '4'), -- This is the Kia Rio5 2012 car data.
('2012', 'Fiat', '500', 'Italy', '4', '1.4', '34', '15500', '2'), -- This is the Fiat 500 2012 car data.
('2012', 'VW', 'Jetta', 'Germany', '4', '2', '29', '15515', '4'), -- This is the VW Jetta 2012 car data.
('2013', 'VW', 'Jetta', 'Germany', '4', '2', '29', '15454', '4'), -- This is the VW Jetta 2013 car data.
('2012', 'Mitsubishi', 'Lancer', 'Japan', '4', '2', '29', '15695', '4'), -- This is the Mitsubishi Lancer 2012 car data.
('2013', 'Scion', 'xD', 'Japan', '4', '1.8', '30', '15745', '4'), -- This is the Scion xD 2013 car data.
('2013', 'Dodge', 'Grand Caravan', 'United States', '6', '3.6', '21', '22030', '4'), -- This is the Dodge Grand Caravan 2013 car data.
('2013', 'Nissan', 'Frontier', 'Japan', '6', '4', '19', '22030', '4'), -- This is the Nissan Frontier 2013 car data.
('2013', 'Ford', 'Mustang', 'United States', '6', '3.7', '24', '22200', '2'), -- This is the Ford Mustang 2013 car data.
('2012', 'Ferrari', 'California', 'Italy', '8', '4.3', '16', '195840', '2'), -- This is the Ferrari California 2012 car data
('2013', 'Ferrari', 'California', 'Italy', '8', '4.3', '16', '198190', '2'), -- This is the Ferrari California 2013 car data.
('2013', 'Mercedes', 'SLS-AMG', 'Germany', '8', '6.2', '16', '199500', '2'), -- This is the Mercedes SLS-AMG 2013 car data.
('2012', 'Ferrari', '458 Italia', 'Italy', '8', '4.5', '15', '229825', '2'), -- This is the Ferrari 458 Italia 2012 car data.
('2012', 'Bentley', 'Mulsanne', 'United Kingdom', '8', '6.8', '14', '290000', '4'), -- This is the Bentley Mulsanne 2012 car data.
('2013', 'Nissan', 'GT-R', 'Japan', '6', '3.8', '19', '96820', '2'), -- This is the Nissan GT-R 2013 car data.
('2012', 'BMW', 'ActiveHybrid 750', 'Germany', '6', '3.8', '19', '96820', '2'), -- This is the BMW ActiveHybrid 750 2012 car data.
('2013', 'BMW', 'M6', 'Germany', '8', '4.4', '17', '108350', '2'), -- This is the BMW M6 2013 car data.
('2013', 'Audi', 'S8', 'Germany', '8', '4', '20', '110000', '4'), -- This is the Audi S8 2013 car data.
('2013', 'Lexus', 'LX 570', 'Japan', '8', '5.7', '14', '81530', '4'), -- This is the Lexus LX 570 2013 car data.
('2013', 'BMW', '750', 'Germany', '8', '4.4', '21', '86800', '4'), -- This is the BMW 750 2013 car data.
('2012', 'BMW', 'ActiveHybrid 5', 'Germany', '6', '3', '26', '60950', '4'), -- This is the BMW ActiveHybrid 5 2012 car data.
('2012', 'VW', 'Touareg Hybrid', 'Germany', '6', '3', '22', '61995', '4'), -- This is the VW Touareg Hybrid 2012 car data.
('2012', 'Lotus', 'Evora', 'United Kingdom', '6', '3.5', '22', '66100', '2'), -- This is the Lotus Evora 2012 car data.
('2012', 'Porsche', 'Cayenne Hybrid', 'Germany', '6', '3', '22', '69000', '4') -- This is the Porsche Cayenne Hybrid 2012 car data.
;
 
SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE Country = "United States"; -- This will display the table where the country of every car will be equal to the United States, which is where it is from.

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE Country = "Germany"; -- This will display the table where the country of every car will be equal to the Germany, which is where it is from.

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE MPG >= 20 ORDER BY Price ASC; -- This will display a table where the MPG is greater than or equal to 20 and it will be ordered by the price in ascending order.

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE Price > 10000 AND Price < 20000 ORDER BY Model DESC; -- This is displaying a table where the price is greater than 10000 and the price is less than 20000. It will be ordered by the model in descening order.

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE Price > 20000 AND Price < 30000 ORDER BY Price ASC; -- This is displaying a table where the price of the car is greater than 20000, but the price is less than 30000. It will be ordered by the price in ascending order. 

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE Price > 30000 ORDER BY MPG ASC; -- This is displaying a table where the price of the car is greater than 30000. The order will be by the MPG in ascending order.

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE Country="Germany" AND Cycl = 6 -- This will display a table where the cars will be from germany and the cyclinder will be equal to 6.
ORDER BY Make ASC; -- This will order the make, which is where it is from by ascending order.

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE MPG > 30 ORDER BY Model DESC; -- This will make the MPG of the car greater than 30. Then the data will be ordered by the Model in descending order.

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE MPG < 20 ORDER BY Price DESC; -- This will display a table where the MPG will be less than 20 and will be ordered by the price in descending order.

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
WHERE Cycl = 8 ORDER BY Make DESC; -- This will display a table where the cyclinder is equal to 8 and it will be ordered by the make of the car being in descending order.

SELECT Years, Make, Model, Country, Cycl, Engine, MPG, Price, Doors -- This is showing that all the columns except for the primary key are being selected.
FROM CarsData -- This is taking the columns and the data from the table called CarsData. This is telling the user where to get the data from.
Order BY Make DESC, Model ASC; -- This will display a table where the order of the Make of what brand the car will will be order in descending order and the model of the car will be ordered in ascending order.

drop database Cars; -- This will delete the database called Cars, which will also delete the table, CarsData because the table is inside the database.
























